package cn.clwxy.gdz.service;

import java.util.List;

import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;

import cn.clwxy.gdz.bo.TeacherInfoBean;
import cn.clwxy.gdz.util.BaseDao;

public class TeacherInfoBeanServiceImpl extends BaseDao implements
		TeacherInfoBeanService {

	@Override
	public TeacherInfoBean getTeacherInfos(Integer teacherId) {
		String sql = "SELECT nt.teacherId,nt.teacherRealName,nt.sjn subjectName,group_concat(g.gradeName) gradeName,ta.addressDesc,ad.addressName,t.selfmark,t.teachYear,tt.type,t.teacherUniversity,t.certificate,t.teacherSex,t.workType,t.other,t.teacherLevel,i.src,t.teacherSpeciality "
				+ " from (SELECT t.teacherId,t.teacherRealName,group_concat(sj.subjectName) as sjn"
				+ " from teacher t"
				+ " INNER JOIN teachersubjectrelative ts  ON t.teacherId=ts.teacherId"
				+ " INNER JOIN `subject` sj ON ts.subjectId=sj.subjectId"
				+ " GROUP BY t.teacherId ORDER BY t.teacherId) as nt "
				+ " INNER JOIN teachergraderelative tg ON nt.teacherId=tg.teacherId"
				+ " INNER JOIN grade g ON tg.gradeId=g.id "
				+ " INNER JOIN teacher t ON nt.teacherId=t.teacherId "
				+ " INNER JOIN teacheraddress ta ON t.teacherId=ta.teacherId"
				+ " INNER JOIN addresscounty ad ON ta.addressCountryId=ad.addressId "
				+ " INNER JOIN teachertype tt ON t.teacherType=tt.id "
				+ " INNER JOIN imgid  i  ON t.teacherImgId=i.id "
				+ " WHERE nt.teacherId=? GROUP BY nt.teacherId";
		return super.querySingle(sql, new BeanHandler<TeacherInfoBean>(
				TeacherInfoBean.class), teacherId);
	}
}
